package com.aaa.dao.impl;

import com.aaa.dao.UserDao;
import com.aaa.entity.Role;
import com.aaa.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Repository
public class UserDaoImpl implements UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    private RowMapper<User> rowMapper=new RowMapper<User>() {
        public User mapRow(ResultSet resultSet, int i) throws SQLException {
            User user=new User();
            user.setId(resultSet.getInt("id"));
            user.setUsername(resultSet.getString("username"));
            user.setPassword(resultSet.getString("password"));
            user.setPhone(resultSet.getString("phone"));
            user.setCreate_date(resultSet.getString("create_date"));
            return user;
        }
    };

    /**
     * 根据用户名和密码查询用户
     * @param user
     * @return
     */
    public User checkUsernameAndPassword(User user) {
        String sql="select id,username,password,phone,create_date from user where username=? and password=?";
        List<User> result = jdbcTemplate.query(sql, rowMapper, user.getUsername(), user.getPassword());
        if(result.size()>0) {
            return result.get(0);
        }else{
            return null;
        }
    }


//    private RowMapper<User> rowMapper1=new RowMapper<User>() {
//        public User mapRow(ResultSet resultSet, int i) throws SQLException {
//            User user=new User();
//            user.setId(resultSet.getInt("u.id"));
//            user.setUsername(resultSet.getString("username"));
//            user.setPassword(resultSet.getString("password"));
//            user.setPhone(resultSet.getString("phone"));
//            user.setCreate_date(resultSet.getString("create_date"));
//            Role role=new Role();
//            role.setId(resultSet.getInt("r.id"));
//            role.setName(resultSet.getString("r.name"));
//            user.setRole(role);
//            return user;
//        }
//    };

//    public List<User> listPage(String keyword,Integer offset,Integer limit) {
//        String sql="select distinct * from user u left join user_role u_r on u.id=u_r.u_id\n" +
//                "\t\t\t\t\t\t\t\t\t\tleft join role r on r.id=u_r.r_id where u.username like ? ORDER BY u.id desc limit ?,?";
//        List<User> result = jdbcTemplate.query(sql, rowMapper,"%"+keyword+"%",offset,limit);
//        return result;
//    }

    public List<User> listPage(String keyword,Integer offset,Integer limit) {
        String sql="select distinct * from user where username like ? ORDER BY id desc limit ?,?";
        List<User> result = jdbcTemplate.query(sql, rowMapper,"%"+keyword+"%",offset,limit);
        return result;
    }

    public Integer count(){
        String sql="select count(id) as c from user";
        List<Integer> count = jdbcTemplate.query(sql, new RowMapper<Integer>() {
            public Integer mapRow(ResultSet resultSet, int i) throws SQLException {
                return resultSet.getInt("c");
            }
        });
        return count.get(0);
    }

    public Integer save(User user) {
        String sql="insert into user(username,phone,create_date) values(?,?,?)";
        int count = jdbcTemplate.update(sql, user.getUsername(), user.getPhone(), user.getCreate_date());
        return count;
    }

    public Integer update(User user) {
        String sql="update user set username=?,phone=? where id=?";
        int count = jdbcTemplate.update(sql, user.getUsername(), user.getPhone(), user.getId());

        return count;
    }

    public Integer checkUsername(String username) {
        String sql="select count(id) as c from user where username=?";
        List<Integer> count = jdbcTemplate.query(sql, new RowMapper<Integer>() {
            public Integer mapRow(ResultSet resultSet, int i) throws SQLException {
                return resultSet.getInt("c");
            }
        },username);
        return count.get(0);
    }

    public Integer checkUsername(String username,Integer id) {
        String sql="select count(id) as c from user where username=? and id!=?";
        List<Integer> count = jdbcTemplate.query(sql, new RowMapper<Integer>() {
            public Integer mapRow(ResultSet resultSet, int i) throws SQLException {
                return resultSet.getInt("c");
            }
        },username,id);
        return count.get(0);
    }

    public Integer delete(Integer id) {
        String sql="delete from user where id=?";
        int count = jdbcTemplate.update(sql,id);

        return count;
    }

    public Integer saveRole(Integer uid,Integer rid) {
        String sql="insert into user_role values(?,?)";
        int count = jdbcTemplate.update(sql, uid, rid);
        return count;
    }

    public Integer deleteRole(Integer uid){
        String sql="delete from user_role where u_id=?";
        int update = this.jdbcTemplate.update(sql, uid);
        return 1;
    }

    public Integer updatePassword(User user) {
        String sql="update user set password=? where id=?";
        Integer count=jdbcTemplate.update(sql,user.getPassword(),user.getId());
        return count;
    }

}
